package dbi.org.api;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;

import dbi.org.exception.ColumnAlreadyExistsException;
import dbi.org.exception.ColumnDoesntExistsException;
import dbi.org.exception.ColumnNameEmptyException;
import dbi.org.exception.ConstraintIsNull;
import dbi.org.exception.ConstraintsAlreadyExistsException;
import dbi.org.exception.DbAlreadyExistsException;
import dbi.org.exception.DbDoesntExistsException;
import dbi.org.exception.DbNameDoesntMatchException;
import dbi.org.exception.DbNameEmptyException;
import dbi.org.exception.InvalidKeyException;
import dbi.org.exception.KeyColumnCannotBeEmptyException;
import dbi.org.exception.KeyDoesntExistsException;
import dbi.org.exception.PrimaryKeyColumnDoesntExist;
import dbi.org.exception.PrimaryKeyDoesntExist;
import dbi.org.exception.PrimaryKeyHasNoColumns;
import dbi.org.exception.PrimaryKeyValueAlreadyExists;
import dbi.org.exception.RecordNamesAndValuesNotEqual;
import dbi.org.exception.RecordValueTypeDoesntExist;
import dbi.org.exception.ReferenceColumnTypesAreNotEqual;
import dbi.org.exception.ReferenceColumnsAreNotEqual;
import dbi.org.exception.ReferenceFromGroupDoesntExist;
import dbi.org.exception.ReferenceRecordException;
import dbi.org.exception.ReferenceToGroupDoesntExist;
import dbi.org.exception.ReferenceToTableExists;
import dbi.org.exception.TableAlreadyExistsException;
import dbi.org.exception.TableCannotBeDropped;
import dbi.org.exception.TableDoesntExistsException;
import dbi.org.exception.TableDoesntMatchException;
import dbi.org.exception.TableNameEmptyException;
import dbi.org.exception.TypeDoesntExistsException;
import dbi.org.exception.ValueCannotBeEmptyException;
import dbi.org.exception.ValueNotFoundException;
import dbi.org.model.Column;
import dbi.org.model.Constraint;
import dbi.org.model.Database;
import dbi.org.model.ForeignKey;
import dbi.org.model.GroupKey;
import dbi.org.model.PrimaryKey;
import dbi.org.model.Record;
import dbi.org.model.Reference;
import dbi.org.model.Table;
import dbi.org.model.UniqueKey;

public class RDBMS {

	private LinkedHashMap<String, Database> databases;
	// Constraints wordt gebruikt om makkelijk te controleren of er een geen
	// dubbele constraints bestaan met dezelfde naam
	private LinkedHashMap<String, Constraint> constraints;
	private Connection connection = null;

	private static RDBMS instance = null;
	private DAO dao;

	private RDBMS() {
		databases = new LinkedHashMap<String, Database>();
		constraints = new LinkedHashMap<String, Constraint>();
		ConnectionManager cm = ConnectionManager.getInstance();
		connection = cm.getConnection();

	}

	public static RDBMS getInstance() {
		if (instance == null)
			instance = new RDBMS();
		return instance;
	}

	public void init() {
		dao = DAO.getInstance();
	}
	
	public void clear() {
		instance = null;
	}
	public void clearDAO() {
		if(dao != null) {
		this.dao.clear();
		}
	}

	/**
	 * Returns the databases
	 * 
	 * @return
	 */
	public LinkedHashMap<String, Database> getDatabases() {
		return databases;
	}

	/**
	 * Returns the constraints
	 * 
	 * @return constraints
	 */
	public LinkedHashMap<String, Constraint> getConstraints() {
		return constraints;
	}

	/**
	 * Finds the correct database
	 * 
	 * @param database
	 * @return
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 */
	private Database findDatabase(String database)
			throws DbDoesntExistsException, DbNameEmptyException {

		if (!database.equals("")) {
			Database db = databases.get(database);
			if (db != null) {
				return db;
			}
			throw new DbDoesntExistsException();
		}
		throw new DbNameEmptyException();

	}

	/**
	 * Creates a database
	 * 
	 * @param name
	 * @throws DbAlreadyExistsException
	 * @throws DbNameEmptyException
	 */
	public void createDatabase(String name) throws DbAlreadyExistsException,
			DbNameEmptyException {
		if (!name.equals("")) {
			// checks
			if (databases.get(name) == null) {
				databases.put(name, new Database(name));
			} else {
				throw new DbAlreadyExistsException(name);
			}
		} else {
			throw new DbNameEmptyException();
		}
	}

	/**
	 * Creates a table
	 * 
	 * @param name
	 * @param database
	 * @throws DbNameEmptyException
	 * @throws DbDoesntExistsException
	 * @throws DbNameDoesntMatchException
	 * @throws TableAlreadyExistsException
	 * @throws ConstraintsAlreadyExistsException
	 * @throws Exception
	 */
	public void createTable(String constraintNaam, String eenTabelNaam,
			String eenDatabasenaam) throws DbDoesntExistsException,
			DbNameEmptyException, TableAlreadyExistsException,
			DbNameDoesntMatchException, ConstraintsAlreadyExistsException {

		if (!constraints.containsKey(constraintNaam)) {
			if (databases.containsKey(eenDatabasenaam)) {
				if (!databases.get(eenDatabasenaam).getTables()
						.containsKey(eenTabelNaam)) {
					Constraint cons = new Constraint(constraintNaam);
					constraints.put(constraintNaam, cons);
					Database db = databases.get(eenDatabasenaam);
					Table tbl = new Table(eenTabelNaam, db.getName());
					tbl.setConstraint(cons); // Belangrijk de constraint
												// toevoegen zodat deze later
												// opgezocht kan worden
					db.addTable(tbl);
				} else {
					throw new TableAlreadyExistsException(eenTabelNaam);
				}
			} else {
				// Database bestaat niet exceptie
				throw new DbDoesntExistsException();
			}
		} else {
			// Contraint bestaal al exceptie
			throw new ConstraintsAlreadyExistsException(constraintNaam);

		}
	}

	/**
	 * Creates a field
	 * 
	 * @param columnName
	 * @param tableName
	 * @param dbName
	 * @param theType
	 * @param mandatory
	 * @throws DbDoesntExistsException
	 * @throws TableDoesntExistsException
	 * @throws DbNameDoesntMatchException
	 * @throws TableDoesntMatchException
	 * @throws ColumnAlreadyExistsException
	 * @throws TableNameEmptyException
	 * @throws DbNameEmptyException
	 * @throws TypeDoesntExistsException
	 */
	public void createColumn(String columnName, String tableName,
			String dbName, String theType, Boolean mandatory)
			throws DbDoesntExistsException, TableDoesntExistsException,
			DbNameDoesntMatchException, TableDoesntMatchException,
			ColumnAlreadyExistsException, TableNameEmptyException,
			DbNameEmptyException, TypeDoesntExistsException {

		// Database db = findDatabase(dbName);
		// Table tbl = db.findTable(tableName);
		// tbl.addColumn(new Column(columnName, tbl.getTableName(),
		// db.getName(),
		// theType, mandatory));

		if (databases.containsKey(dbName)) {
			if (databases.get(dbName).getTables().containsKey(tableName)) {
				if (!databases.get(dbName).getTables().get(tableName)
						.getColumns().containsKey(columnName)) {
					databases
							.get(dbName)
							.getTables()
							.get(tableName)
							.addColumn(
									new Column(columnName, tableName, dbName,
											theType, mandatory));
				} else {
					throw new ColumnAlreadyExistsException(columnName);
				}
			} else {
				throw new TableDoesntExistsException();
			}
		} else {
			throw new DbDoesntExistsException();
		}
	}

	/**
	 * Drops the database
	 * 
	 * @param dbName
	 * @throws DbNameEmptyException
	 * @throws DbDoesntExistsException
	 */
	public void dropDatabase(String dbName) throws DbDoesntExistsException,
			DbNameEmptyException {
		Database db = findDatabase(dbName);

		databases.remove(db.getName());
	}

	/**
	 * Drops the table
	 * 
	 * @param dbName
	 * @param tblName
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws TableCannotBeDropped 
	 * @throws ReferenceToTableExists 
	 */
	public void dropTable(String dbName, String tblName)
			throws TableNameEmptyException, TableDoesntExistsException,
			DbDoesntExistsException, DbNameEmptyException, TableCannotBeDropped, ReferenceToTableExists {

		Database db = findDatabase(dbName);
		db.removeTable(tblName);
	}

	/**
	 * Drops a field
	 * 
	 * @param dbName
	 * @param tblName
	 * @param kolomName
	 * @throws DbDoesntExistsException
	 * @throws ColumnDoesntExistsException
	 * @throws ColumnNameEmptyException
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws DbNameEmptyException
	 */
	public void dropColumn(String dbName, String tblName, String kolomName)
			throws DbDoesntExistsException, ColumnDoesntExistsException,
			ColumnNameEmptyException, TableNameEmptyException,
			TableDoesntExistsException, DbNameEmptyException {

		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		tbl.removeColumn(kolomName);

	}

	/**
	 * Alters the database name
	 * 
	 * @param dbOldName
	 * @param dbNewName
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 */
	public void alterDatabase(String dbOldName, String dbNewName)
			throws DbDoesntExistsException, DbNameEmptyException {
		Database db = findDatabase(dbOldName);

		db.setName(dbNewName);

		databases.remove(dbOldName);
		databases.put(dbNewName, db);
		// loop trough all tables and fields
		if (db.getTables().size() > 0) {
			for (Table table : db.getTables().values()) {
				table.setDbName(dbNewName);
				for(GroupKey key : table.getConstraint().getKeys().values()) {
					key.setDbName(dbNewName);
				}
				if (table.getColumns().size() > 0) {
					for (Column column : table.getColumns().values()) {
						column.setDbName(dbNewName);
					}
				}

			}
		}
	

	}

	/**
	 * Prints the table with colums
	 * @param dbName
	 * @param tblName
	 * @throws DbNameEmptyException 
	 * @throws DbDoesntExistsException 
	 * @throws TableDoesntExistsException 
	 * @throws TableNameEmptyException 
	 */
	public void showTable(String dbName, String tblName) throws DbDoesntExistsException, DbNameEmptyException, TableNameEmptyException, TableDoesntExistsException{
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		
		tbl.showRecords();
	}
	/**
	 * Alters the table name
	 * 
	 * @param dbName
	 * @param tblOldName
	 * @param tblNewName
	 * @throws DbDoesntExistsException
	 * @throws TableDoesntExistsException
	 * @throws TableAlreadyExistsException
	 * @throws DbNameDoesntMatchException
	 * @throws TableNameEmptyException
	 * @throws DbNameEmptyException
	 * @throws TableCannotBeDropped 
	 * @throws TableDoesntMatchException 
	 */
	public void alterTable(String dbName, String tblOldName, String tblNewName)
			throws DbDoesntExistsException, TableDoesntExistsException,
			TableAlreadyExistsException, DbNameDoesntMatchException,
			TableNameEmptyException, DbNameEmptyException, TableCannotBeDropped, TableDoesntMatchException {
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblOldName);
		tbl.setTableName(tblNewName);
		// Verwijderen uit lijst en opnieuw toevoegen aan lijst vantabellen
		
		for(Column column : tbl.getColumns().values()) {
			column.setTable(tblNewName);
		}

		for(GroupKey key : tbl.getConstraint().getKeys().values()) {
			key.setTableName(tblNewName);
		}
		//Moet dit?
//		for(Reference ref : tbl.getConstraint().getReferences().values()) {
//		
//		}

		db.changeTableName(tblOldName, tbl);
	}

	/**
	 * Alters the Field name
	 * 
	 * @param dbName
	 * @param tblName
	 * @param oldColumnName
	 * @param newColumnName
	 * @throws DbDoesntExistsException
	 * @throws ColumnDoesntExistsException
	 * @throws TableDoesntExistsException
	 * @throws ColumnNameEmptyException
	 * @throws DbNameDoesntMatchException
	 * @throws TableDoesntMatchException
	 * @throws ColumnAlreadyExistsException
	 * @throws TableNameEmptyException
	 * @throws DbNameEmptyException
	 */
	public void alterColumn(String dbName, String tblName,
			String oldColumnName, String newColumnName)
			throws DbDoesntExistsException, ColumnDoesntExistsException,
			TableDoesntExistsException, ColumnNameEmptyException,
			DbNameDoesntMatchException, TableDoesntMatchException,
			ColumnAlreadyExistsException, TableNameEmptyException,
			DbNameEmptyException {

		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		Column veld = tbl.findKolom(oldColumnName);
		veld.setName(newColumnName);

		tbl.removeColumn(oldColumnName);
		tbl.addColumn(veld);
	}

	/**
	 * Adds a primary key
	 * 
	 * @param name
	 * @param dbName
	 * @param tblName
	 * @param columnName
	 * @throws DbDoesntExistsException
	 * @throws TableDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws TableNameEmptyException
	 * @throws ColumnNameEmptyException
	 * @throws ColumnDoesntExistsException
	 * @throws TableDoesntMatchException
	 * @throws DbNameDoesntMatchException
	 * @throws KeyColumnCannotBeEmptyException
	 */
	public void addPrimaryKey(String name, String dbName, String tblName,
			String columnName) throws DbDoesntExistsException,
			TableDoesntExistsException, DbNameEmptyException,
			TableNameEmptyException, ColumnDoesntExistsException,
			ColumnNameEmptyException, DbNameDoesntMatchException,
			TableDoesntMatchException, KeyColumnCannotBeEmptyException {
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		Column veld = tbl.findKolom(columnName);

		PrimaryKey primkey = new PrimaryKey(name, db.getName(),
				tbl.getTableName());
		primkey.addColumn(veld);
	}

	/**
	 * Deletes all databases
	 */
	public void dropDatabases() {
		databases.clear();
	}
	
	/**
	 * Adds a single value in a table
	 * @param dbName
	 * @param tblName
	 * @param columnName
	 * @param value
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws ValueCannotBeEmptyException
	 * @throws ColumnDoesntExistsException
	 * @throws RecordNamesAndValuesNotEqual
	 * @throws RecordValueTypeDoesntExist
	 * @throws PrimaryKeyColumnDoesntExist
	 * @throws PrimaryKeyDoesntExist
	 * @throws ReferenceToGroupDoesntExist
	 * @throws PrimaryKeyHasNoColumns
	 * @throws ConstraintIsNull
	 * @throws PrimaryKeyValueAlreadyExists 
	 */
	public void addSingleValue(String dbName, String tblName, String columnName, String value) throws TableNameEmptyException, TableDoesntExistsException, DbDoesntExistsException, DbNameEmptyException, ValueCannotBeEmptyException, ColumnDoesntExistsException, RecordNamesAndValuesNotEqual, RecordValueTypeDoesntExist, PrimaryKeyColumnDoesntExist, PrimaryKeyDoesntExist, ReferenceToGroupDoesntExist, PrimaryKeyHasNoColumns, ConstraintIsNull, PrimaryKeyValueAlreadyExists{
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		
		String[] names = new String[1];
		names[0] = columnName;
		
		String[] values = new String[1];
		values[0] = value;
		tbl.addRecord(names, values);
	}
	
	/**
	 * Adds multiple values in a table.
	 * @param dbName
	 * @param tblName
	 * @param names
	 * @param values
	 * @throws ColumnDoesntExistsException
	 * @throws RecordNamesAndValuesNotEqual
	 * @throws ValueCannotBeEmptyException
	 * @throws RecordValueTypeDoesntExist
	 * @throws PrimaryKeyColumnDoesntExist
	 * @throws PrimaryKeyDoesntExist
	 * @throws ReferenceToGroupDoesntExist
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws PrimaryKeyHasNoColumns
	 * @throws ConstraintIsNull
	 * @throws PrimaryKeyValueAlreadyExists 
	 */
	public void addMultiValues(String dbName, String tblName, String[] names, String[] values) throws ColumnDoesntExistsException, RecordNamesAndValuesNotEqual, ValueCannotBeEmptyException, RecordValueTypeDoesntExist, PrimaryKeyColumnDoesntExist, PrimaryKeyDoesntExist, ReferenceToGroupDoesntExist, TableNameEmptyException, TableDoesntExistsException, DbDoesntExistsException, DbNameEmptyException, PrimaryKeyHasNoColumns, ConstraintIsNull, PrimaryKeyValueAlreadyExists {
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		tbl.addRecord(names, values);
	}

	// DML
	/**
	 * Selects all records from a given db and table
	 * @param dbName
	 * @param tblName
	 * @return
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws ValueNotFoundException
	 */
	public ArrayList<Record> selectAll(String dbName, String tblName) throws DbDoesntExistsException, DbNameEmptyException, TableNameEmptyException, TableDoesntExistsException, ValueNotFoundException {
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		return tbl.getRecords();
	}
	
	/**
	 * Selects records limited by a value
	 * @param dbName
	 * @param tblName
	 * @param limit
	 * @return
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws ValueNotFoundException
	 */
	public ArrayList<Record> selectLimit(String dbName, String tblName, int limit) throws DbDoesntExistsException, DbNameEmptyException, TableNameEmptyException, TableDoesntExistsException, ValueNotFoundException {
		ArrayList<Record> limitResult = new ArrayList<Record>();
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		
		int i = 0;
		for(Record record : tbl.getRecords()){
			if(i == limit){
				break;
			}else{
				limitResult.add(record);
			}
			i++;
		
		}
		return limitResult;
	}

	/**
	 * Selects all records who'll match the where clause
	 * @param dbName
	 * @param tblName
	 * @param columnName
	 * @param columnValue
	 * @return
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws ValueNotFoundException
	 * @throws ColumnDoesntExistsException
	 */
	public ArrayList<Record> selectAllWhere(String dbName, String tblName, String columnName, String columnValue ) throws DbDoesntExistsException, DbNameEmptyException, TableNameEmptyException, TableDoesntExistsException, ValueNotFoundException, ColumnDoesntExistsException {
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		Column cm = tbl.getColumns().get(columnName);
		return tbl.selectRecords(columnName, columnValue);
	
	}
	
	/**
	 * Selects all records from table. Limit defines max number to select
	 * @param dbName
	 * @param tblName
	 * @param columnName
	 * @param columnValue
	 * @param limit
	 * @return
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws ValueNotFoundException
	 * @throws ColumnDoesntExistsException
	 */
	public ArrayList<Record> selectAllWhereLimit(String dbName, String tblName, String columnName, String columnValue, int limit ) throws DbDoesntExistsException, DbNameEmptyException, TableNameEmptyException, TableDoesntExistsException, ValueNotFoundException, ColumnDoesntExistsException {
		ArrayList<Record> limitResult = new ArrayList<Record>();
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		Column cm = tbl.getColumns().get(columnName);
		
		int i = 0;
		for(Record record : tbl.selectRecords(columnName, columnValue)){
			if(i == limit){
				break;
			}else{
				limitResult.add(record);
			}
			i++;
		
		}
		return limitResult;
	}
	
	/**
	 * Deletes all records in given table
	 * @param dbName
	 * @param tblName
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws ColumnDoesntExistsException
	 * @throws ReferenceRecordException 
	 */
	public void deleteAll(String dbName, String tblName) throws DbDoesntExistsException, DbNameEmptyException, TableNameEmptyException, TableDoesntExistsException, ColumnDoesntExistsException, ReferenceRecordException{
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		tbl.deleteRecords("", "");
	}
	
	/**
	 * Deletes all records which matches the column key name
	 * @param dbName
	 * @param tblName
	 * @param key
	 * @param value
	 * @throws DbDoesntExistsException
	 * @throws DbNameEmptyException
	 * @throws TableNameEmptyException
	 * @throws TableDoesntExistsException
	 * @throws ColumnDoesntExistsException
	 * @throws ReferenceRecordException 
	 */
	public void deleteWhere(String dbName, String tblName, String key, String value) throws DbDoesntExistsException, DbNameEmptyException, TableNameEmptyException, TableDoesntExistsException, ColumnDoesntExistsException, ReferenceRecordException{
		Database db = findDatabase(dbName);
		Table tbl = db.findTable(tblName);
		tbl.deleteRecords(key, value);
	}

	/**
	 * 
	 * @param consName
	 * @param keyOrRefName
	 * @param groupName
	 * @param columns
	 * @throws DbNameDoesntMatchException
	 * @throws TableDoesntMatchException
	 * @throws KeyColumnCannotBeEmptyException
	 * @throws InvalidKeyException
	 * @throws KeyDoesntExistsException
	 */
	public void createConstraint(String consName, String keyOrRefName,
			String groupName, HashMap<String, Column> columns)
			throws DbNameDoesntMatchException, TableDoesntMatchException,
			KeyColumnCannotBeEmptyException, InvalidKeyException,
			KeyDoesntExistsException {
		GroupKey myKey = null;
		if (columns == null){
			throw new NullPointerException("No columns found");
		}
		else {

			for (Database db : databases.values()) {
				for (Table tbl : db.getTables().values()) {
					if (tbl.getConstraint().getName().equals(consName)) {
						if (!tbl.getConstraint().getKeys()
								.containsKey(groupName)) {
							if (keyOrRefName.startsWith("UK")) {
								myKey = new UniqueKey(groupName, db.getName(),
										tbl.getTableName());
								myKey.addMultipleColumn(columns);
								tbl.getConstraint().addSingleKeys(myKey);
							} else if (keyOrRefName.startsWith("FK")) {
								myKey = new ForeignKey(groupName, db.getName(),
										tbl.getTableName());
								myKey.addMultipleColumn(columns);
								tbl.getConstraint().addSingleKeys(myKey);
							} else if (keyOrRefName.startsWith("PK")) {
								myKey = new PrimaryKey(groupName,
										db.getName(), tbl.getTableName());
								myKey.addMultipleColumn(columns);
								tbl.getConstraint().addSingleKeys(myKey);
							} else if (keyOrRefName.startsWith("REF")) {
								Reference ref = new Reference(groupName, null,
										null);
								tbl.getConstraint().addsingleReference(ref);
							} else
								throw new InvalidKeyException(keyOrRefName
										+ " " + groupName);
						} else {
							tbl.getConstraint().getKey(keyOrRefName)
									.addMultipleColumn(columns);
						}

					}

				}
			}

		}

	}

	/**
	 * 
	 * @param refNaam
	 * @param fromGrp
	 * @param toGrp
	 * @throws ReferenceToGroupDoesntExist
	 * @throws ReferenceFromGroupDoesntExist
	 * @throws ReferenceColumnsAreNotEqual
	 * @throws ReferenceColumnTypesAreNotEqual
	 */
	public void createReference(String refNaam, String fromGrp, String toGrp)
			throws ReferenceToGroupDoesntExist, ReferenceFromGroupDoesntExist,
			ReferenceColumnsAreNotEqual, ReferenceColumnTypesAreNotEqual {

		GroupKey fromGroup = null;
		GroupKey toGroup = null;
		Table tabletoAdd = null;

		for (Database database : databases.values()) {
			for (Table tabel : database.getTables().values()) {
				for (GroupKey key : tabel.getConstraint().getKeys().values()) {
					if (key.getName().equals(fromGrp)) {
						
						fromGroup = key;						
					}

					if (key.getName().equals(toGrp)) {
						toGroup = key;
						tabletoAdd = tabel;
					}
				}
			}
		}
		
		// check of de groups wel bestaan
		if (fromGroup != null && toGroup != null) {
			// check of er evenveel kolummen zijn
			//System.out.println("Reference: " + tabletoAdd.getTableName() + " " + fromGroup.getName() + " " + toGroup.getName());
			ArrayList<Column> fromColumns = new ArrayList<Column>();
			ArrayList<Column> toColumns = new ArrayList<Column>();

			fromColumns.addAll(fromGroup.getColumns().values());
			toColumns.addAll(toGroup.getColumns().values());

			if (fromGroup.getColumns().size() == toGroup.getColumns().size()) {
				// Zijn de kolummen gelijk
				for (int aantalColumns = 0; aantalColumns < fromGroup
						.getColumns().size(); aantalColumns++) {
					// Vergelijken van de kolummen moeten gelijk zijn aan mekaar
					if (fromColumns.get(aantalColumns).getColumntype() != toColumns.get(aantalColumns).getColumntype()) {
						throw new ReferenceColumnTypesAreNotEqual();
					}
				}
				
				tabletoAdd.getConstraint().addsingleReference(
						new Reference(refNaam, fromGroup, toGroup));

			} else {
				throw new ReferenceColumnsAreNotEqual();

			}
		} else {
			if (fromGroup == null) {
				throw new ReferenceFromGroupDoesntExist();
			}
			if (toGroup == null) {
				throw new ReferenceToGroupDoesntExist("Referentie naar groep is niet goed");
			}

		}
	}
	
	/**
	 * Drops all constraints
	 */
	public void dropContraints() {
		constraints.clear();

	}
	
	/**
	 * Finds a column
	 * @param columnName
	 * @param dbName
	 * @param tableName
	 * @return
	 * @throws TypeDoesntExistsException
	 * @throws ColumnDoesntExistsException
	 * @throws TableDoesntExistsException
	 * @throws DbDoesntExistsException
	 */
	public Column getColumn(String columnName, String dbName, String tableName) throws TypeDoesntExistsException, ColumnDoesntExistsException, TableDoesntExistsException, DbDoesntExistsException {
		//Het ophalen van de desbetreffende velden uit de RDMS
		Column column = null;
		if(getDatabases().containsKey(dbName)) {
			if(getDatabases().get(dbName).getTables().containsKey(tableName)) {
				if(getDatabases().get(dbName).getTables().get(tableName).getColumns().containsKey(columnName)) {
					column =  getDatabases().get(dbName).getTables().get(tableName).getColumns().get(columnName);
				} else {
					throw new ColumnDoesntExistsException("Column bestaat niet");
				}				
			} else {
				throw new TableDoesntExistsException();
			}
		} else {
			throw new DbDoesntExistsException();
		}		
		return column;
	}
}
